CLEAR SCREEN
-- literal.sql
--
-- (c) 2009 Oracle Corporation
--
-- Author: Bridesmaid
--
-- Example of use of dbms_assert.enquote_literal


--CONNECT sys/oracle AS SYSDBA
set echo on
DROP USER testuser CASCADE
/

DROP USER eviluser CASCADE
/

GRANT
  Unlimited Tablespace,
  Create Session,
  Create Table,
  Create Procedure
TO testuser identified by testuser
/
GRANT
  Unlimited Tablespace,
  Create Session,
  Create Table,
  Create Procedure
TO eviluser identified by eviluser
/

--CONNECT testuser/testuser
SET SERVEROUTPUT ON

CREATE TABLE t(a varchar2(10))
/

BEGIN
	INSERT INTO t (a) values ('a');
	INSERT INTO t (a) values ('b');
	INSERT INTO t (a) values ('b');
	INSERT INTO t (a) values ('c''d');
	commit;
END;
/

CREATE OR REPLACE PROCEDURE Count_Rows(w in varchar2) 
authid definer as

	-- Useful constant

	Quote	constant varchar2(1) := '''';

	-- The statement we will execute

	Stmt 	constant varchar2(32767) :=
		'SELECT count(*) FROM t WHERE a='||
			Quote||w||Quote;

	-- The count of rows returned by the statement
			
	Row_Count	number;
BEGIN
	EXECUTE IMMEDIATE Stmt INTO Row_Count;
	DBMS_OUTPUT.PUT_LINE(Row_Count||' rows');
END;
/

BEGIN Count_Rows('a'); END;
/

BEGIN Count_Rows('b'); END;
/

BEGIN Count_Rows('c''''d'); END;
/

BEGIN Count_Rows('x'); END;
/

GRANT EXECUTE ON Count_Rows TO PUBLIC
/

connect eviluser/eviluser

SET SERVEROUTPUT ON

CREATE OR REPLACE FUNCTION f RETURN varchar2 authid current_user as
	pragma autonomous_transaction;
BEGIN
	-- Execute immediate because t is not granted to public
	EXECUTE IMMEDIATE 'DELETE FROM t';
	COMMIT;
	RETURN 'a';
END;
/

GRANT EXECUTE ON f TO PUBLIC
/


BEGIN testuser.Count_Rows('a'' and eviluser.f=''a'); END;
/

connect testuser/testuser
SET SERVEROUTPUT ON
SELECT * FROM t
/

BEGIN
	INSERT INTO t (a) values ('a');
	INSERT INTO t (a) values ('b');
	INSERT INTO t (a) values ('b');
	INSERT INTO t (a) values ('c''d');
	commit;
END;
/


CREATE OR REPLACE PROCEDURE Count_Rows(w in varchar2) 
authid definer as

	-- Useful constants

	Quote		constant varchar2(1) := '''';
	Quote_Quote	constant varchar2(2) := Quote||Quote;

	Safe_Literal	varchar2(32767) :=
		Quote||replace(w,Quote,Quote_Quote)||Quote;

	-- The statement we will execute

	Stmt 	constant varchar2(32767) :=
		'SELECT count(*) FROM t WHERE a='||
			DBMS_ASSERT.ENQUOTE_LITERAL(Safe_Literal);

	-- The count of rows returned by the statement
			
	Row_Count	number;
BEGIN
	EXECUTE IMMEDIATE Stmt INTO Row_Count;
	DBMS_OUTPUT.PUT_LINE(Row_Count||' rows');
END;
/

BEGIN Count_Rows('a'); END;
/

BEGIN Count_Rows('b'); END;
/

BEGIN Count_Rows('c''''d'); END;
/

BEGIN Count_Rows('c''d'); END;
/

BEGIN Count_Rows('x'); END;
/

-- Now lets repeat the attack

connect eviluser/eviluser
SET SERVEROUTPUT ON

BEGIN testuser.Count_Rows('a'' and eviluser.f=''a'); END;
/

BEGIN testuser.Count_Rows('b'); END;
/

connect testuser/testuser

set serveroutput on

SELECT * FROM t
/

